-- @owner: @zhanghuan96
-- @date: 2024/11/5
-- @testpoint: GMS_XMLGEN.GETXMLTYPE语法2，参数2取值范围测试，部分合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:建表;expect:成功
drop table if exists t_getxmltype0008;
CREATE TABLE t_getxmltype0008 (
    id SERIAL PRIMARY KEY,
    binary_data BYTEA,
    bit_field BIT(8),
    xml_data XML
);
INSERT INTO t_getxmltype0008 (binary_data, bit_field, xml_data)
VALUES (
    decode('48656c6c6f20576f726c64', 'hex'),  
    B'10101010',                              
    '<note><to>User</to><from>Admin</from><heading>Reminder</heading><body>Don''t forget the meeting!</body></note>'
);
SELECT * FROM t_getxmltype0008;
--step2:参数为-1;expect:报错
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', -1);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(50); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step3:参数1为2^32;expect:报错
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 2^32);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(50); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step4:参数2是null;expect:报错
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', null);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(50); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step5:小数;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 4294967295.4);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step6:小数，成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 4294967295.5);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step7:参数2为表达式;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 42949+154);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step8:参数2为数值加引号;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', '42949');
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step9:参数2为分数;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 42949/464);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step10:参数2为0;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 0);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step11:参数2为2^32-1;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 2^32-1);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step12:参数2为字符串;expect:报错
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 'abc');
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step13:参数2含特殊字符;expect:报错
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', '测试*^%');
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step14:参数2为布尔类型;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', true);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step15:参数2为类型转换;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 145646::number);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step16:结果的类型长度小于实际结果;expect:截断
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', 145646::number);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(50); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step17:参数2为无法转换的类型;expect:报错
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('select * from t_getxmltype0008', '2024-12-21'::number);
RAISE INFO 'INFO:XML Output: %', xml_type::varchar2(500); 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step18:清理环境;expect:成功
drop table if exists t_getxmltype0008;
drop extension if exists gms_xmlgen;